REM Program: colrenam.sql REM Author : David Wendelken REM Tactics, Inc. (404) 248-1226 REM Part of the Tactics' Designer/2000 Toolbox REM Genesis: 10-Nov-1995 REM 08-Jan-1996 Added 'validate mode' REM REM Purpose: To rename columns back to their source column names. This is REM only done for foreign key columns. The intent is to 'correct' REM the names created by the Database Design Wizard. REM REM Note : I always back up before running a utility like this. REM REM Warning: This program will not create duplicate column names, it will REM instead provide a listing of 'errors', i.e., column names it REM could not rename. REM Warning: Do not run this if others are changing column names or foreign REM key constraints in the same application or an application which REM shares those objects with it. REM Warning: This utility does not support column prefixes. Feel free to REM upgrade it and send it back to me! REM Warning: Although this can be run repeatedly for a given application, REM it will override any manually crafted foreign key column names REM for the table(s)/column(s) that meet the selection criteria. PROMPT Enter the Application System Name DEFINE p_application_system_name='&1' PROMPT Enter the Application System Version DEFINE p_application_system_version='&2' PROMPT Enter Table Name or % for ALL DEFINE p_table_name='&3' PROMPT Enter Column Name or % for ALL DEFINE p_column_name='&4' Prompt (U)pdate names or just (S)how and tell DEFINE p_update_or_show='&5' PROMPT Enter the output filename (without the .lis extension) DEFINE p_filename='&6' REM This is a wide report, so set the line width accordingly. set linesize 132 set pagesize 66 REM Let's not clutter up the screen and page with program code. REM set verify off REM set echo off REM set feedback off REM Force diagnostic output on so that the user can see what progress REM is being made and what the program did. REM Increase the size of this buffer if your application is very large REM and you get a buffer-overflow error. SET SERVEROUTPUT ON SIZE 400000 SPOOL &p_filename..lis DECLARE -- rename_columns last_table_id ci_table_definitions.id%TYPE; search_column_id ci_columns.id%TYPE; new_column_id ci_columns.id%TYPE; new_fk_count NUMBER; new_column_name ci_columns.name%TYPE; new_table_name ci_table_definitions.name%TYPE; cannot_init_appsys EXCEPTION; -- Application not available or no -- access. temp_column_name ci_columns.name%TYPE; -- To hold the new column -- name so that we can safely check -- its length. duplicate_column_msg VARCHAR2(15); prefix_text VARCHAR2(30); suffix_text VARCHAR2(30); -- An array of column_names in a table to check for duplicates on new names. max_column_array_idx NUMBER; TYPE column_name_tabtype IS TABLE OF ci_columns.name%TYPE NOT NULL INDEX BY BINARY_INTEGER; -- An array of column_ids to allow proper identification of the name array above. TYPE column_id_tabtype IS TABLE OF ci_columns.id%TYPE NOT NULL INDEX BY BINARY_INTEGER; column_name_array column_name_tabtype; column_id_array column_id_tabtype; -- Used to clear the arrays above. An odd quirk of PL/SQL (until a later version -- corrects it) is that an array can only be cleared by assigning it the value of -- another, empty array. Go figure! empty_column_name_array column_name_tabtype; empty_column_id_array column_id_tabtype; -- A cursor that will find all foreign key columns. CURSOR find_fk_columns IS SELECT RTRIM(citabd_src.name) src_table_name ,RTRIM(cicol_src.name) src_column_name ,cicol_src.id src_column_id ,citabd_src.id src_table_id FROM ci_columns cicol_src ,ci_table_definitions citabd_src ,ci_application_systems ciapps WHERE citabd_src.name LIKE UPPER('&p_table_name') AND cicol_src.name LIKE UPPER('&p_column_name') AND citabd_src.id = cicol_src.table_reference AND ciapps.id = citabd_src.application_system_owned_by AND ciapps.name = UPPER('&p_application_system_name') AND ciapps.version = &p_application_system_version AND EXISTS (SELECT 'x' FROM ci_key_components cikeyc ,ci_foreign_key_constraints ciforkc WHERE cicol_src.id = cikeyc.column_reference AND cikeyc.foreign_column_reference IS NOT null AND ciforkc.id = cikeyc.constraint_reference ) ORDER BY citabd_src.name, cicol_src.sequence_number; -- When learning a PL/SQL program, it's best to start with the Main -- program section at the bottom of this PL/SQL block, then refer back -- to the inline functions and procedures as needed. /************* Inline Function ***********************************/ FUNCTION find_fk_count (p_search_column_id IN NUMBER) RETURN NUMBER IS new_fk_count NUMBER; BEGIN SELECT count(*) INTO new_fk_count FROM ci_columns cicol_fk ,ci_key_components cikeyc ,ci_foreign_key_constraints ciforkc WHERE cikeyc.column_reference = p_search_column_id AND cicol_fk.id = cikeyc.foreign_column_reference AND ciforkc.id = cikeyc.constraint_reference; RETURN new_fk_count; END; /************* End of Inline Function ****************************/ /************* Inline Function ***********************************/ FUNCTION find_fk_id (p_search_column_id IN NUMBER) RETURN NUMBER IS new_column_id ci_columns.id%TYPE; BEGIN SELECT cicol_fk.id INTO new_column_id FROM ci_columns cicol_fk ,ci_key_components cikeyc ,ci_foreign_key_constraints ciforkc WHERE cikeyc.column_reference = p_search_column_id AND cicol_fk.id = cikeyc.foreign_column_reference AND ciforkc.id = cikeyc.constraint_reference; RETURN new_column_id; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); DBMS_OUTPUT.PUT_LINE('FIND_FK_ID : ' || p_search_column_id); END; /************* End of Inline Function ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE find_fk_prefix_and_suffix (p_col_id IN NUMBER ,p_fk_prefix OUT VARCHAR2 ,p_fk_suffix OUT VARCHAR2 ) IS new_relend_id ci_relationship_ends.id%TYPE; CURSOR find_prefix IS SELECT UPPER(SUBSTR(cdt.txt_text,9,INSTR(cdt.txt_text,'::',9,1) - 9)) FROM cdi_text cdt WHERE cdt.txt_ref = new_relend_id AND cdt.txt_type = 'CDINOT' AND UPPER(cdt.txt_text) like 'PREFIX::%::' ORDER BY cdt.txt_seq; CURSOR find_suffix IS SELECT UPPER(SUBSTR(cdt.txt_text,9,INSTR(cdt.txt_text,'::',9,1) - 9)) FROM cdi_text cdt WHERE cdt.txt_ref = new_relend_id AND cdt.txt_type = 'CDINOT' AND UPPER(cdt.txt_text) like 'SUFFIX::%::' ORDER BY cdt.txt_seq; BEGIN p_fk_prefix := NULL; p_fk_suffix := NULL; SELECT ciforkc.relationship_end_reference INTO new_relend_id FROM ci_columns cicol ,ci_key_components cikeyc ,ci_foreign_key_constraints ciforkc WHERE cicol.id = p_col_id AND cicol.id = cikeyc.column_reference AND ciforkc.id = cikeyc.constraint_reference; BEGIN OPEN find_prefix; FETCH find_prefix INTO p_fk_prefix; CLOSE find_prefix; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; BEGIN OPEN find_suffix; FETCH find_suffix INTO p_fk_suffix; CLOSE find_suffix; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); DBMS_OUTPUT.PUT_LINE('FIND_FK_PREFIX_AND_SUFFIX : ' || p_col_id); END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE find_fk_column_data (p_new_column_id IN NUMBER ,p_new_table_name OUT VARCHAR2 ,p_new_column_name OUT VARCHAR2 ) IS BEGIN SELECT RTRIM(citabd_fk.name) fk_table_name ,RTRIM(cicol_fk.name) fk_column_name INTO p_new_table_name ,p_new_column_name FROM ci_columns cicol_fk ,ci_table_definitions citabd_fk WHERE cicol_fk.id = p_new_column_id AND citabd_fk.id = cicol_fk.table_reference; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); DBMS_OUTPUT.PUT_LINE('FIND_FK_COLUMN_DATA : ' || p_new_column_id); END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE update_column_name (p_new_column_id IN OUT NUMBER ,p_new_column_name IN OUT VARCHAR2 ) IS column_to_update ciocolumn.data; activity_status VARCHAR2(1); activity_warning VARCHAR2(1); BEGIN -- Open an activity -- This issues a savepoint, so incomplete changes can be rolled back to here. CDAPI.OPEN_ACTIVITY; -- Set column values and their indicators. column_to_update.v.name := UPPER(RTRIM(p_new_column_name)); column_to_update.i.name := true; -- Update the column record. CIOCOLUMN.UPD(p_new_column_id, column_to_update); -- Validate the column insert. CDAPI.VALIDATE_ACTIVITY(activity_status, activity_warning); -- Show violation messages here. FOR each_violation IN (SELECT * FROM ci_violations) LOOP DBMS_OUTPUT.PUT_LINE(CDAPI.INSTANTIATE_MESSAGE(each_violation.facility ,each_violation.code ,each_violation.p0 ,each_violation.p1 ,each_violation.p2 ,each_violation.p3 ,each_violation.p4 ,each_violation.p5 ,each_violation.p6 ,each_violation.p7 ) ); END LOOP; -- Attempt to close the activity. CDAPI.CLOSE_ACTIVITY(activity_status); -- If activity did not close, then abort and roll back changes. IF activity_status != 'Y' THEN CDAPI.ABORT_ACTIVITY; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); IF cdapi.activity IS NOT null THEN cdapi.abort_activity; END IF; END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE output_src_column (p_src_table_name IN VARCHAR2 ,p_src_column_name IN VARCHAR2 ) IS BEGIN DBMS_OUTPUT.PUT_LINE(p_src_table_name || '.' || p_src_column_name); END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE output_new_column (p_new_table_name IN VARCHAR2 ,p_new_column_name IN VARCHAR2 ) IS BEGIN DBMS_OUTPUT.PUT_LINE('<---' || p_new_table_name || '.' || p_new_column_name); END; /************* End of Inline Procedure ****************************/ /************* Inline Function ***********************************/ FUNCTION check_column_names (p_src_table_id IN VARCHAR2 ,p_src_column_name IN VARCHAR2 ,p_new_column_name IN VARCHAR2 ,p_src_column_id IN NUMBER ) RETURN VARCHAR2 IS number_of_duplicate_columns NUMBER; update_array_idx NUMBER; BEGIN number_of_duplicate_columns := 0; IF p_new_column_name = p_src_column_name THEN RETURN 'Old = New'; ELSE FOR array_idx IN 1 .. max_column_array_idx LOOP IF p_new_column_name = column_name_array(array_idx) THEN number_of_duplicate_columns := number_of_duplicate_columns + 1; END IF; IF p_src_column_id = column_id_array(array_idx) THEN update_array_idx := array_idx; END IF; END LOOP; IF number_of_duplicate_columns > 0 THEN RETURN 'Too Many'; ELSE -- update the name so that it is included in the checks. column_name_array(update_array_idx) := p_new_column_name; RETURN 'OK'; END IF; END IF; END; /************* End of Inline Function ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE setup_column_name_array (p_src_table_id IN VARCHAR2 ) IS CURSOR find_column_names IS SELECT RTRIM(cicol_src.name) column_name ,cicol_src.id column_id FROM ci_columns cicol_src ,ci_table_definitions citabd_src ,ci_application_systems ciapps WHERE citabd_src.id = p_src_table_id AND citabd_src.id = cicol_src.table_reference AND ciapps.id = citabd_src.application_system_owned_by AND ciapps.name = UPPER('&p_application_system_name') AND ciapps.version = &p_application_system_version ORDER BY cicol_src.sequence_number; BEGIN max_column_array_idx := 0; -- empty out the arrays. column_name_array := empty_column_name_array; column_id_array := empty_column_id_array; FOR column_name_record IN find_column_names LOOP max_column_array_idx := max_column_array_idx + 1; column_name_array(max_column_array_idx) := column_name_record.column_name; column_id_array(max_column_array_idx) := column_name_record.column_id; END LOOP; dbms_output.put_line('max cna idx = ' || max_column_array_idx); END; /************* End of Inline Procedure ****************************/ /************* Start of Main Program Body ************************/ BEGIN BEGIN -- Initialize the Designer/2000 API. IF NOT cdapi.initialized THEN -- Not already initialized. CDAPI.INITIALIZE(UPPER('&p_application_system_name') ,&p_application_system_version); END IF; EXCEPTION WHEN OTHERS THEN RAISE cannot_init_appsys; END; last_table_id := 0; -- Fetch specified foreign key columns. FOR column_record IN find_fk_columns LOOP -- We use a PL/SQL array (table) to store all column names in the -- table definition that we are in. We check this array later for -- duplicate column names when we want to change a column name. IF last_table_id != column_record.src_table_id THEN setup_column_name_array(column_record.src_table_id); last_table_id := column_record.src_table_id; END IF; OUTPUT_SRC_COLUMN(column_record.src_table_name ,column_record.src_column_name ); search_column_id := column_record.src_column_id; LOOP -- We will perform this loop on a per column basis. -- Find how many foreign key lineages there are, or if we have -- identified an ultimate ancestor column. Since we have, by -- definition, started our search with foreign key columns, there -- should always be at least one ancestor. There may be more than -- one lineage path from the foreign key column (or one of its ancestors). -- If so, we abort changes on the column, as that is a more complicated -- situation than we want to code for. new_fk_count := FIND_FK_COUNT(search_column_id); IF new_fk_count = 0 THEN -- This should not occur the first time in the loop per column. -- We have an ultimate ancestor column we can grab a name from! new_column_id := search_column_id; FIND_FK_COLUMN_DATA(new_column_id, new_table_name, new_column_name); FIND_FK_PREFIX_AND_SUFFIX(column_record.src_column_id ,prefix_text ,suffix_text ); new_column_name := prefix_text || new_column_name || suffix_text; OUTPUT_NEW_COLUMN(new_table_name,new_column_name); -- Is the new name too long to update? IF LENGTH(new_column_name) > 40 THEN DBMS_OUTPUT.PUT_LINE(' ERROR: New Column Name too long!'); ELSE -- Check for duplicate column names. duplicate_column_msg := CHECK_COLUMN_NAMES(column_record.src_table_id ,column_record.src_column_name ,new_column_name ,column_record.src_column_id ); IF duplicate_column_msg = 'Too Many' THEN DBMS_OUTPUT.PUT_LINE(' ERROR: Duplicate New Name : ' || new_column_name ); ELSIF duplicate_column_msg = 'Old = New' THEN DBMS_OUTPUT.PUT_LINE(' FYI: Same Name.'); ELSE DBMS_OUTPUT.PUT_LINE(' FYI: Changed Name.'); -- Update database if instructed to do so. IF UPPER('&p_update_or_show') = 'U' THEN UPDATE_COLUMN_NAME(column_record.src_column_id ,new_column_name); END IF; END IF; END IF; EXIT; ELSIF new_fk_count > 1 THEN -- This foreign key column has more than one parent. Our -- utility doesn't handle that. OUTPUT_SRC_COLUMN(column_record.src_table_name ,column_record.src_column_name ); DBMS_OUTPUT.PUT_LINE('ERROR: Too many FK choices from this column!'); EXIT; ELSE -- We have a chain of fk columns to follow, but we are not at an -- ultimate ancestor column yet. search_column_id := FIND_FK_ID(search_column_id); END IF; END LOOP; END LOOP; -- All changes made. DBMS_OUTPUT.PUT_LINE('DONE'); EXCEPTION WHEN cannot_init_appsys THEN DBMS_OUTPUT.PUT_LINE('Cannot Initialize API for the Application System: ' || UPPER('&p_application_system_name') ); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); END; -- rename_columns / REM While we're at it, let's report on all existing column names that are too REM long to use as Oracle column names. PROMPT These column names are over 30 characters long. They must be PROMPT shortened. PROMPT SELECT RTRIM(citabd.name) table_name ,RTRIM(cicol.name) column_name FROM ci_columns cicol ,ci_table_definitions citabd ,ci_application_systems ciapps WHERE citabd.name LIKE UPPER('&p_table_name') AND cicol.name LIKE UPPER('&p_column_name') AND citabd.id = cicol.table_reference AND ciapps.id = citabd.application_system_owned_by AND ciapps.name = UPPER('&p_application_system_name') AND ciapps.version = &p_application_system_version AND LENGTH(cicol.name) > 30 ORDER BY 1,2; SPOOL OFF